<?php

require __DIR__ . '/../bootstrap.php';
require_once __DIR__ . "/../auth.php";
require_once __DIR__ . "/functions.php";
$em = require __DIR__ . '/../example/em.php';

$auth = new \Auth\Auth($em);
$user = $auth->getUser();

//$_GET['dateBegin'] = '29.07.2013';
//$_GET['dateEnd'] = '05.08.2013';


//BEGIN KERCH ///////////////////////////////////////////////////////
if (isset($_GET['dateBegin'])) {
    $dateBegin = convertDate($_GET['dateBegin']);
}

if (isset($_GET['dateEnd'])) {
    $dateEnd = convertDate($_GET['dateEnd']);
}

/*
echo '<pre>';
print_r($dateBegin);
echo '<pre>';
echo '<pre>';
print_r($dateEnd);
echo '<pre>';
*/

$sql = "SELECT sim.number
     , user.name
     , user.id
     , user_group.name AS group_name
FROM
  sim
INNER JOIN biling
ON biling.phone = sim.id
INNER JOIN sim_owner
ON sim_owner.sim_id = sim.id
INNER JOIN user
ON sim_owner.user_id = user.id
INNER JOIN user_group
ON user.group_id = user_group.id
WHERE
  biling.`date` BETWEEN '$dateBegin 00:00:00' AND '$dateEnd 23:59:59'
  AND sim_owner.state = 1
              AND user.lft >= {$user->getLeft()}
              AND user.rgt <= {$user->getRight()}
GROUP BY
  sim.number";

$number_array = $em->getConnection()->query($sql)->fetchAll();
/*
echo '<pre>';
print_r($number_array);
echo '</pre>';
exit();
*/

function get_last_date($number, $type, $em, $dateBegin, $dateEnd, $user)
{
    $sql = "SELECT MAX(biling.`date`) AS date_" . $type . "
, biling.paysystem
FROM
  biling
INNER JOIN sim
ON biling.phone = sim.id
INNER JOIN sim_owner
ON sim_owner.sim_id = sim.id
INNER JOIN user
ON sim_owner.user_id = user.id
WHERE
biling.`date` BETWEEN '" . $dateBegin . " 00:00:00' AND '$dateEnd 23:59:59'
  AND sim.number = " . $number . "
  AND biling.type = '$type'
  AND sim_owner.state = 1
  AND user.lft >= {$user->getLeft()}
  AND user.rgt <= {$user->getRight()}";
    $result = $em->getConnection()->query($sql)->fetchAll();
    if ($type != 'FILLUP') {
        $result = implode('', $result[0]);
    }


    /*
        echo '<pre>';
        print_r($result);
        echo '<pre>';
    */
    return $result;
}

function get_amount_sum($number, $em, $dateBegin, $dateEnd, $user)
{
    $sql = "SELECT sum(biling.amount) AS sum_amount
FROM
  biling
INNER JOIN sim
ON biling.phone = sim.id
INNER JOIN sim_owner
ON sim_owner.sim_id = sim.id
INNER JOIN user
ON sim_owner.user_id = user.id
WHERE
  biling.`date` BETWEEN '$dateBegin 00:00:00' AND '$dateEnd 23:59:59'
AND sim.number = " . $number . "
AND sim_owner.state = 1
              AND user.lft >= {$user->getLeft()}
              AND user.rgt <= {$user->getRight()}
AND biling.type = 'FILLUP'";
    $result = $em->getConnection()->query($sql)->fetchAll();
    $result = implode('', $result[0]);
    return $result;
}

function get_count_orig($number, $em, $dateBegin, $dateEnd, $user)
{
    $sql = "SELECT count(biling.type) AS count_orig
FROM
  biling
INNER JOIN sim
ON biling.phone = sim.id
INNER JOIN sim_owner
ON sim_owner.sim_id = sim.id
INNER JOIN user
ON sim_owner.user_id = user.id
WHERE
    biling.`date` BETWEEN '$dateBegin 00:00:00' AND '$dateEnd 23:59:59'
AND sim.number = $number
AND sim_owner.state = 1
              AND user.lft >= {$user->getLeft()}
              AND user.rgt <= {$user->getRight()}
  AND biling.type = 'ORIG'";
    $result = $em->getConnection()->query($sql)->fetchAll();
    $result = implode('', $result[0]);
    return $result;
}


function get_count_term($number, $em, $dateBegin, $dateEnd, $user)
{
    $sql = "SELECT count(biling.type) AS count_orig
FROM
  biling
INNER JOIN sim
ON biling.phone = sim.id
INNER JOIN sim_owner
ON sim_owner.sim_id = sim.id
INNER JOIN user
ON sim_owner.user_id = user.id
WHERE
    biling.`date` BETWEEN '$dateBegin 00:00:00' AND '$dateEnd 23:59:59'
AND sim.number = " . $number . "
AND sim_owner.state = 1
              AND user.lft >= {$user->getLeft()}
              AND user.rgt <= {$user->getRight()}
  AND biling.type = 'TERM'";
    $result = $em->getConnection()->query($sql)->fetchAll();
    $result = implode('', $result[0]);
    return $result;
}

function get_sum_orig_duration($number, $em, $dateBegin, $dateEnd, $user)
{
    $sql = "SELECT sum(biling.duration) AS orig_duration
FROM
  biling
INNER JOIN sim
ON biling.phone = sim.id
INNER JOIN sim_owner
ON sim_owner.sim_id = sim.id
INNER JOIN user
ON sim_owner.user_id = user.id
WHERE
    biling.`date` BETWEEN '$dateBegin 00:00:00' AND '$dateEnd 23:59:59'
AND sim.number = " . $number . "
AND sim_owner.state = 1
              AND user.lft >= {$user->getLeft()}
              AND user.rgt <= {$user->getRight()}
  AND biling.type = 'ORIG'";
    $result = $em->getConnection()->query($sql)->fetchAll();
    $result = implode('', $result[0]);

    $seconds = $result;
    $h = floor($seconds / 3600);
    $m = floor(($seconds % 3600) / 60);
    $s = ($seconds % 3600) % 60;
    $result = $h . " ч . " . $m . " мин . " . $s . " сек .";
    return $result;
}

function get_sum_term_duration($number, $em, $dateBegin, $dateEnd, $user)
{
    $sql = "SELECT sum(biling.duration) AS term_duration
FROM
  biling
INNER JOIN sim
ON biling.phone = sim.id
INNER JOIN sim_owner
ON sim_owner.sim_id = sim.id
INNER JOIN user
ON sim_owner.user_id = user.id
WHERE
    biling.`date` BETWEEN '$dateBegin 00:00:00' AND '$dateEnd 23:59:59'
AND sim.number = " . $number . "
AND sim_owner.state = 1
              AND user.lft >= {$user->getLeft()}
              AND user.rgt <= {$user->getRight()}
  AND biling.type = 'TERM'";
    $result = $em->getConnection()->query($sql)->fetchAll();
    $result = implode('', $result[0]);

    $seconds = $result;
    $h = floor($seconds / 3600);
    $m = floor(($seconds % 3600) / 60);
    $s = ($seconds % 3600) % 60;
    $result = $h . " ч . " . $m . " мин . " . $s . " сек .";
    return $result;
}

function get_last_date_fillup($df)
{
    return $df[0]['date_FILLUP'];
}

function get_last_type_fillup($tf)
{
    return $tf[0]['paysystem'];
}

$result["aaData"] = array();
$i = 0;
foreach ($number_array as $number) {
    $i++;
    $decode = (79988 + $number['id']) * 22;
    if ($user->getId() == 1) {
        $name_user = sprintf('<a href="index.php?page=user_profile&id=' . $decode . '">' . $number['name'] . '</a>');
    } else {
        $name_user = $number['name'];
        }
    $result["aaData"][] = array(
        $i,
        $number['number'],
        $name_user,
        $number['group_name'],
        get_last_date($number['number'], $type = 'ORIG', $em, $dateBegin, $dateEnd, $user),
        get_last_date($number['number'], $type = 'TERM', $em, $dateBegin, $dateEnd, $user),
        get_last_date_fillup($df = (get_last_date($number['number'], $type = 'FILLUP', $em, $dateBegin, $dateEnd, $user))),
        get_last_type_fillup($tf = (get_last_date($number['number'], $type = 'FILLUP', $em, $dateBegin, $dateEnd, $user))),
        get_amount_sum($number['number'], $em, $dateBegin, $dateEnd, $user),
        get_count_orig($number['number'], $em, $dateBegin, $dateEnd, $user),
        get_count_term($number['number'], $em, $dateBegin, $dateEnd, $user),
        get_sum_orig_duration($number['number'], $em, $dateBegin, $dateEnd, $user),
        get_sum_term_duration($number['number'], $em, $dateBegin, $dateEnd, $user)
    );
}
echo json_encode($result);
exit();
//END KERCH ///////////////////////////////////////////////////////